#coding = utf-8
#12.21 ga1400 属性 保存到数据库中
import json
import sqlite3
import time,datetime
import os
from ga_Objects import *

class GAPICLIB():
    def __init__(self, dbname='ga.db'):   
        if dbname:
            self.con = sqlite3.connect(dbname)
        else:
            self.con = sqlite3.connect(':memory:')
        self.cur = self.con.cursor()
        try:
            self.facetab = 'facetab'
            self.persontab = 'persontab'
            self.motorVehicletab = 'motorVehicletab'
            self.nonMotorVehicletab = 'nonMotorVehicletab'
            self.imagetab = 'imagetab'
            self.filetab = 'filetab'
            self.videotab = 'videotab'
            
            tags = 'timestamp text,fromwhere text'        
            for k in imageInfo_list:
                tags += ',' + k + ' text'   
            sqlstr = 'create table {}(id integer primary key autoincrement not null,{})'.format(self.imagetab, tags)
            self.cur.execute(sqlstr)  
            
            tags = 'timestamp text,fromwhere text'        
            for k in fileInfo_list:
                tags += ',' + k + ' text'   
            sqlstr = 'create table {}(id integer primary key autoincrement not null,{})'.format(self.filetab, tags)
            self.cur.execute(sqlstr)  
            
            tags = 'timestamp text,fromwhere text'        
            for k in videoSliceInfo_list:
                tags += ',' + k + ' text'   
            sqlstr = 'create table {}(id integer primary key autoincrement not null,{})'.format(self.videotab, tags)
            self.cur.execute(sqlstr)  
            
            tags = 'timestamp text,fromwhere text'        
            for k in faceObject_list:
                tags += ',' + k + ' text'   
            sqlstr = 'create table {}(id integer primary key autoincrement not null,{})'.format(self.facetab, tags)        
            self.cur.execute(sqlstr)  
            
            tags = 'timestamp text,fromwhere text'        
            for k in personObject_list:
                tags += ',' + k + ' text'   
            sqlstr = 'create table {}(id integer primary key autoincrement not null,{})'.format(self.persontab, tags)
            self.cur.execute(sqlstr)  
            
            tags = 'timestamp text,fromwhere text'        
            for k in motorVehicleObject_list:
                tags += ',' + k + ' text'   
            sqlstr = 'create table {}(id integer primary key autoincrement not null,{})'.format(self.motorVehicletab, tags)
            self.cur.execute(sqlstr)  
            
            tags = 'timestamp text,fromwhere text'        
            for k in nonMotorVehicleObject_list:
                tags += ',' + k + ' text'   
            sqlstr = 'create table {}(id integer primary key autoincrement not null,{})'.format(self.nonMotorVehicletab, tags)
            self.cur.execute(sqlstr)  
            
            
            self.con.commit()
            print('创建一张表 %s...'%self.facetab)
            print('创建一张表 %s...'%self.persontab)
            print('创建一张表 %s...'%self.motorVehicletab)
            print('创建一张表 %s...'%self.nonMotorVehicletab)
            print('创建一张表 %s...'%self.imagetab)
            print('创建一张表 %s...'%self.filetab)
            print('创建一张表 %s...'%self.videotab)
            self.insert(faceObject_json, faceObject_list, '来源', self.facetab)
            self.insert(personObject_json, personObject_list, '来源', self.persontab)
            self.insert(motorVehicleObject_json, motorVehicleObject_list, '来源', self.motorVehicletab)
            self.insert(nonMotorVehicleObject_json, nonMotorVehicleObject_list, '来源', self.nonMotorVehicletab)
            self.insert(imageInfo_json, imageInfo_list, '来源', self.imagetab)
            self.insert(fileInfo_json, fileInfo_list, '来源', self.filetab)
            self.insert(videoSliceInfo_json, videoSliceInfo_list, '来源', self.videotab)
        except Exception as e:
            pass

    def insert(self, value_json, obj_list, fromwhere, tab):
        #timestamp = time.strftime("%Y%m%d_%H%M%S", time.localtime())
        cur_time = datetime.datetime.now()
        timestamp = "%d%.2d%.2d_%.2d%.2d%.2d.%d"%(cur_time.year,cur_time.month,cur_time.day,cur_time.hour,cur_time.minute,cur_time.second,cur_time.microsecond)
        #fromwhere = 'facelist'
        tags = 'timestamp,fromwhere'   
        zhanwei_tag = '?,?'
        valuelist = [timestamp,fromwhere]
        for k in obj_list:
            tags += ','+str(k) 
            zhanwei_tag += ',?'
            #data += ',' + str(v) + k
            if k == 'SubImageList':
                if value_json.get(k):
                    if isinstance(value_json.get(k), dict):
                        tmp = value_json.get(k).get('SubImageInfoObject')
                        if tmp:
                            if isinstance(tmp, list):
                                subimageinfo_msg = '%d SubImageInfoObject '%len(tmp)
                                for _ in range(len(tmp)):
                                    subimageinfo_msg += "," + tmp[_].get("Type")      
                                valuelist.append(subimageinfo_msg)                                    
                            else:
                                valuelist.append('')
                        else:
                            valuelist.append('')
                    else:
                        valuelist.append('图像列表')
                else:
                    valuelist.append('')
            else:
                valuelist.append(str(value_json.get(k,"")))
        sqlstr = 'insert into {} ({})values({})'.format(tab, tags, zhanwei_tag)
        self.cur.execute(sqlstr,valuelist)
        
        #print('批量插入多条记录...')
        #self.cur.executemany('insert into mytab (name, passwd) values(?,?)',get_data_list(3))
    
    def save(self):
        self.con.commit()

    def show(self):        
        u_select = input("回车显示记录个数, 输入任意字符回车显示所有记录:")
        if u_select == '':
            print('-'*10, self.facetab) 
            self.cur.execute('select count(*) from %s'%self.facetab)            
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.persontab) 
            self.cur.execute('select count(*) from %s'%self.persontab)
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.motorVehicletab) 
            self.cur.execute('select count(*) from %s'%self.motorVehicletab)
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.nonMotorVehicletab) 
            self.cur.execute('select count(*) from %s'%self.nonMotorVehicletab)
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.imagetab) 
            self.cur.execute('select count(*) from %s'%self.imagetab)
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.filetab) 
            self.cur.execute('select count(*) from %s'%self.filetab)
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.videotab) 
            self.cur.execute('select count(*) from %s'%self.videotab)
            for item in self.cur.fetchall():
                print(' ', item)
        else:
            print('显示所有记录...')
            print('-'*10, self.facetab) 
            self.cur.execute('select * from %s'%self.facetab)            
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.persontab) 
            self.cur.execute('select * from %s'%self.persontab)
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.motorVehicletab) 
            self.cur.execute('select * from %s'%self.motorVehicletab)
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.nonMotorVehicletab) 
            self.cur.execute('select * from %s'%self.nonMotorVehicletab)
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.imagetab) 
            self.cur.execute('select * from %s'%self.imagetab)
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.filetab) 
            self.cur.execute('select * from %s'%self.filetab)
            for item in self.cur.fetchall():
                print(' ', item)
            print('-'*10, self.videotab) 
            self.cur.execute('select * from %s'%self.videotab)
            for item in self.cur.fetchall():
                print(' ', item)
            
    def delete(self):        
        print('删除一条记录...')
        self.cur.execute('delete from facetab where id=?',(1,))
        self.con.commit()
        
    def close(self):        
        self.cur.close()
        self.con.close()
            
def insert_data(jsondata, gapic_lib):
    # 插入数据
    # jsondata ，字典数据
    # gapic_lib，对象
    if gapic_lib:
        try:
            if jsondata.get('FaceListObject'):
                obj = jsondata.get('FaceListObject').get('FaceObject')
                if isinstance(obj, list):
                    for x in obj:
                        gapic_lib.insert(x, faceObject_list, 'FaceListObject->FaceObject', gapic_lib.facetab)
            if jsondata.get('PersonListObject'):
                obj = jsondata.get('PersonListObject').get('PersonObject')
                if isinstance(obj, list):
                    for x in obj:
                        gapic_lib.insert(x, personObject_list, 'PersonListObject->PersonObject', gapic_lib.persontab)
            if jsondata.get('MotorVehicleListObject'):
                obj = jsondata.get('MotorVehicleListObject').get('MotorVehicleObject')
                if isinstance(obj, list):
                    for x in obj:
                        gapic_lib.insert(x, motorVehicleObject_list, 'MotorVehicleListObject->MotorVehicleObject', gapic_lib.motorVehicletab)
            if jsondata.get('NonMotorVehicleListObject'):
                obj = jsondata.get('NonMotorVehicleListObject').get('NonMotorVehicleObject')
                if isinstance(obj, list):
                    for x in obj:
                        gapic_lib.insert(x, nonMotorVehicleObject_list, 'NonMotorVehicleListObject->NonMotorVehicleObject', gapic_lib.nonMotorVehicletab)

            if jsondata.get('ImageListObject'):
                obj = jsondata.get('ImageListObject').get('Image')
                if isinstance(obj, list):
                    for y in obj: 
                        tmp = y.get('ImageInfo')
                        if tmp:
                            gapic_lib.insert(tmp, imageInfo_list, 'ImageListObject->Image->ImageInfo', gapic_lib.imagetab)
                        tmp = y.get('FaceList')
                        if tmp:
                            if isinstance(tmp.get('FaceObject'), list):
                                for x in tmp.get('FaceObject'):
                                    gapic_lib.insert(x, faceObject_list, 'ImageListObject->Image->FaceList->FaceObject', gapic_lib.facetab)
                        tmp = y.get('PersonList')
                        if tmp:
                            if isinstance(tmp.get('PersonObject'), list):
                                for x in tmp.get('PersonObject'):
                                    gapic_lib.insert(x, personObject_list, 'ImageListObject->Image->PersonList->PersonObject', gapic_lib.persontab)
                        tmp = y.get('MotorVehicleList')
                        if tmp:
                            if isinstance(tmp.get('MotorVehicleObject'), list):
                                for x in tmp.get('MotorVehicleObject'):
                                    gapic_lib.insert(x, motorVehicleObject_list, 'ImageListObject->Image->MotorVehicleList->MotorVehicleObject', gapic_lib.motorVehicletab)
                        tmp = y.get('NonMotorVehicleList')
                        if tmp:
                            if isinstance(tmp.get('NonMotorVehicleObject'), list):
                                for x in tmp.get('NonMotorVehicleObject'):
                                    gapic_lib.insert(x, nonMotorVehicleObject_list, 'ImageListObject->Image->NonMotorVehicleList->NonMotorVehicleObject', gapic_lib.nonMotorVehicletab)

            if jsondata.get('FileListObject'):
                obj = jsondata.get('FileListObject').get('File')
                if isinstance(obj, list):
                    for y in obj: 
                        tmp = y.get('FileInfo')
                        if tmp:
                            gapic_lib.insert(tmp, fileInfo_list, 'FileListObject->File->FileInfo', gapic_lib.filetab)
                        tmp = y.get('FaceList')
                        if tmp:
                            if isinstance(tmp.get('FaceObject'), list):
                                for x in tmp.get('FaceObject'):
                                    gapic_lib.insert(x, faceObject_list, 'FileListObject->File->FaceList->FaceObject', gapic_lib.facetab)
                        tmp = y.get('PersonList')
                        if tmp:
                            if isinstance(tmp.get('PersonObject'), list):
                                for x in tmp.get('PersonObject'):
                                    gapic_lib.insert(x, personObject_list, 'FileListObject->File->PersonList->PersonObject', gapic_lib.persontab)
                        tmp = y.get('MotorVehicleList')
                        if tmp:
                            if isinstance(tmp.get('MotorVehicleObject'), list):
                                for x in tmp.get('MotorVehicleObject'):
                                    gapic_lib.insert(x, motorVehicleObject_list, 'FileListObject->File->MotorVehicleList->MotorVehicleObject', gapic_lib.motorVehicletab)
                        tmp = y.get('NonMotorVehicleList')
                        if tmp:
                            if isinstance(tmp.get('NonMotorVehicleObject'), list):
                                for x in tmp.get('NonMotorVehicleObject'):
                                    gapic_lib.insert(x, nonMotorVehicleObject_list, 'FileListObject->File->NonMotorVehicleList->NonMotorVehicleObject', gapic_lib.nonMotorVehicletab)

            if jsondata.get('VideoSliceListObject'):
                obj = jsondata.get('VideoSliceListObject').get('VideoSlice')
                if isinstance(obj, list):
                    for y in obj: 
                        tmp = y.get('VideoSliceInfo')
                        if tmp:
                            gapic_lib.insert(tmp, videoSliceInfo_list, 'VideoSliceListObject->VideoSlice->VideoSliceInfo', gapic_lib.videotab)
                        tmp = y.get('FaceList')
                        if tmp:
                            if isinstance(tmp.get('FaceObject'), list):
                                for x in tmp.get('FaceObject'):
                                    gapic_lib.insert(x, faceObject_list, 'VideoSliceListObject->VideoSlice->FaceList->FaceObject', gapic_lib.facetab)
                        tmp = y.get('PersonList')
                        if tmp:
                            if isinstance(tmp.get('PersonObject'), list):
                                for x in tmp.get('PersonObject'):
                                    gapic_lib.insert(x, personObject_list, 'VideoSliceListObject->VideoSlice->PersonList->PersonObject', gapic_lib.persontab)
                        tmp = y.get('MotorVehicleList')
                        if tmp:
                            if isinstance(tmp.get('MotorVehicleObject'), list):
                                for x in tmp.get('MotorVehicleObject'):
                                    gapic_lib.insert(x, motorVehicleObject_list, 'VideoSliceListObject->VideoSlice->MotorVehicleList->MotorVehicleObject', gapic_lib.motorVehicletab)
                        tmp = y.get('NonMotorVehicleList')
                        if tmp:
                            if isinstance(tmp.get('NonMotorVehicleObject'), list):
                                for x in tmp.get('NonMotorVehicleObject'):
                                    gapic_lib.insert(x, nonMotorVehicleObject_list, 'VideoSliceListObject->VideoSlice->NonMotorVehicleList->NonMotorVehicleObject', gapic_lib.nonMotorVehicletab)
            return True
        except Exception as e:
            print(e)
    return False
        
def main2():    
    #file = "K:/Server/src/界面5.9/client_data/"+'videos.json' 
    filepath = input("输入json目录:")
    if filepath == '':
        filepath = 'K:\Server\src\界面5.9\client_data' 
    files = []
    filelist = os.listdir(filepath)
    for file in filelist:
        if file.endswith('.json'):
            files.append(file)   
    dbname = 'ga.db'
    gapic_lib = GAPICLIB(dbname=dbname)
    for x in files:    
        file = os.path.join(filepath,x)
        print('-'*10,file)
        with open(file, 'r', encoding='utf8') as f:
            jsondata = json.loads(f.read())
        insert_data(jsondata, gapic_lib)
        gapic_lib.save()
    #gapic_lib.delete()
    gapic_lib.show()
    gapic_lib.close()
             
if __name__ == '__main__': 
    main2()